package com.dasart.snesdbcreater;

import java.io.*;
import java.net.MalformedURLException;
import java.net.URL;
import java.nio.charset.CharsetDecoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class snesdbcreater {

	private static Connection con;
	public static void main(String[] args) {
	     try {	    	 	
		 		try {
					Class.forName("org.hsqldb.jdbcDriver");
				} catch (Exception e) {
					e.printStackTrace();
					// TODO: handle exception
				}
				
				con = DriverManager.getConnection("jdbc:hsqldb:file:database/SNES; shutdown=true","root", "");

				Statement stmt = con.createStatement();
				String sql;
				sql = "CREATE TABLE roms (" +
						"publisher VARCHAR_IGNORECASE(100)," +
						"developer VARCHAR_IGNORECASE(100)," +
						"id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) PRIMARY KEY) SET IGNORECASE TRUE";
				stmt.execute(sql);
				
	    	    // Create a URL for the desired page
				URL url = new URL("http://en.wikipedia.org/wiki/List_of_Super_Nintendo_Entertainment_System_games_%28A-M%29");

	    	    // Read all the text returned by the server
	    	    BufferedReader in = new BufferedReader(new InputStreamReader(url.openStream(), "UTF8"));
	    	    String str;
	    	    String html = "";
	    	    while ((str = in.readLine()) != null) {
	    	        html += str;
	    	    }
	    	    
				Matcher matcher = Pattern.compile("<i><a href=\"/wiki/.*?\"").matcher(html);
				matcher.find();
				List<String> gameTitleUrls = new ArrayList<String>();
				while(matcher.find()){
					gameTitleUrls.add("http://wikipedia.org" + matcher.group().substring(12,matcher.group().length()-1));
				}
				
				for (String gameTitleUrl : gameTitleUrls) {
					html = "";
					try {
						url = new URL(gameTitleUrl);
						in = new BufferedReader(new InputStreamReader(url.openStream(), "UTF-8"));
						html = "";
						str = "";
						while ((str = in.readLine()) != null) {
							html += str;
						}
					} catch (Exception e) {
						//NOP
					}
					
					String developer = getDeveloper(gameTitleUrl, html);
					if(developer != null){developer.replace("'", " ");}
					String publisher = getPublisher(gameTitleUrl, html);
					if(publisher != null){publisher.replace("'", " ");}
					
					
					try {
						stmt = con.createStatement();
						sql = "INSERT INTO roms (publisher, developer) VALUES ('"+publisher+"','"+developer+"')";
						stmt.execute(sql);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				
				
	        in.close();
	    } catch (Exception e) {
	    	//NOP
	    }
	}
	
	private static String getDeveloper(String gameTitleUrl, String html){	
		//Developer REGEX = Developer\(s\)</a></b></td><td><a href="/wiki/Capcom" title="Capcom">[^<]*
		Matcher matcher;
		String developerRaw;
		try {
			matcher = Pattern.compile("Developer\\(s\\)</a></b></td><td>?.*?</").matcher(html);
			matcher.find();
			developerRaw = matcher.group();
		} catch (Exception e) {
			return null;
		}
		
		String dev;
		try {
			matcher.reset();
			matcher = Pattern.compile(">[a-z].*?<",Pattern.CASE_INSENSITIVE).matcher(developerRaw);
			matcher.find();
			dev = matcher.group().substring(1,matcher.group().length()-1);
		} catch (Exception e) {
			return null;
		}
		
		return dev;
	}
	private static String getPublisher(String gameTitleUrl, String html){
		//Publisher REGEX = Publisher\(s\)</a></b></td><td><a href="/wiki/Capcom" title="Capcom">[^<]*
		Matcher matcher;
		String publisherRaw;
		try {
			matcher = Pattern.compile("Publisher\\(s\\)</a></b></td><td>?.*?</").matcher(html);
			matcher.find();
			publisherRaw = matcher.group();
		} catch (Exception e) {
			return null;
		}
		
		String publisher;
		try {
			matcher.reset();
			matcher = Pattern.compile(">[a-z].*?<",Pattern.CASE_INSENSITIVE).matcher(publisherRaw);
			matcher.find();
			publisher = matcher.group().substring(1,matcher.group().length()-1);
		} catch (Exception e) {
			return null;
		}
		
		return publisher;
	}
}

